package com.zhiyou.app.shop.website.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.stereotype.Component;

import com.zhiyou.app.shop.common.util.Page;
import com.zhiyou.app.shop.common.util.ShopConstants;
import com.zhiyou.app.shop.manager.dao.impl.BaseHibernateDAO;
import com.zhiyou.app.shop.website.dao.IMoneyDealDao;
import com.zhiyou.app.shop.website.dao.pojo.MoneyDeal;

@Component
public class MoneyDealDaoImpl extends BaseHibernateDAO<MoneyDeal, String> implements
		IMoneyDealDao {
	
	public MoneyDealDaoImpl(){
		super(MoneyDeal.class);
	}

	public BigDecimal findBonusByDay(final String userId, final String time){
		final String sql = "select sum(md.income) as bonus from money_deal md " +
				"where md.user_id=? and md.account_subject=? and " +
				"date_format(md.create_date,'%Y-%m-%d') =?";
		BigDecimal list = hibernateTemplate.execute(new HibernateCallback<BigDecimal>() {
			public BigDecimal doInHibernate(Session session)
					throws HibernateException {
				SQLQuery query = session.createSQLQuery(sql);
				query.setString(0, userId);
				query.setString(1, ShopConstants.BONUSS);
				query.setString(2, time);
				BigDecimal total = (BigDecimal)query.addScalar("bonus", new org.hibernate.type.BigDecimalType()).uniqueResult();
				if(total ==null){
					return BigDecimal.ZERO;
				}
				return total;
			}
		});
		return list;
	}

	public BigDecimal findBonusByRelationId(final String userId, final String relationId) {
		final String sql = "select md.income as income from money_deal md " +
				"where md.user_id=? and md.account_subject=? and  md.deal_id=? ";
		BigDecimal list = hibernateTemplate.execute(new HibernateCallback<BigDecimal>() {
			public BigDecimal doInHibernate(Session session)
					throws HibernateException {
			SQLQuery query = session.createSQLQuery(sql);
			query.setString(0, userId);
			query.setString(1, ShopConstants.BONUSS);
			query.setString(2, relationId);
			BigDecimal total = (BigDecimal)query.addScalar("income", new org.hibernate.type.BigDecimalType()).uniqueResult();
			if(total ==null){
				return BigDecimal.ZERO;
			}
			return total;
			}
		});
		return list;
	}

	public List findMoneyDeal(final String userAccount,
			final String subjectType, final String startDate, final String endDate, final int start,
			final int pageSize) {
		List list = hibernateTemplate.execute(new HibernateCallback<List>() {
			public List doInHibernate(Session session)
					throws HibernateException {
				String sql = "select u.user_account,a.account_subject,a.account_type,a.income,a.pay,a.create_date " +
							"from money_deal a ,user u where a.user_id =u.user_id ";
				if(userAccount != null && !"".equals(userAccount)){
					sql += " and u.user_account like '%"+userAccount+"%'";
				}
				if(subjectType != null && !"".equals(subjectType)){
					sql += " and a.account_subject '%"+subjectType+"%'";
				}
				if(startDate != null && !"".equals(startDate)){
					
				}
				sql += " order by a.create_date desc ";
				sql += " limit "+start+","+pageSize;
				Query query = session.createSQLQuery(sql);
				query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				return query.list();
			}
		});
		return list;
	}

	public int findMoneyDealCnt(final String userAccount, final String subjectType,
			final String startDate, final String endDate) {
		Integer list = hibernateTemplate.execute(new HibernateCallback<Integer>() {
			public Integer doInHibernate(Session session)
					throws HibernateException {
				String sql = " select count(1) cnt from money_deal a ,user u where a.user_id =u.user_id ";
				if(userAccount != null && !"".equals(userAccount)){
					sql += " and u.user_account like '%"+userAccount+"%'";
				}
				if(subjectType != null && !"".equals(subjectType)){
					sql += " and a.subject_type '"+userAccount+"'";
				}
				if(startDate != null && !"".equals(startDate)){
					
				}
				SQLQuery query = session.createSQLQuery(sql);
				return (Integer)query.addScalar("cnt", new org.hibernate.type.IntegerType()).uniqueResult();
			}
		});
		return list;	
	}

	public BigDecimal getStockExchange(final String userId) {
		final String sql = "select SUM(income) amount from money_deal where account_subject='咖啡豆转游戏豆' and account_type='stock' and user_id=?";
		BigDecimal list = hibernateTemplate.execute(new HibernateCallback<BigDecimal>() {
			public BigDecimal doInHibernate(Session session)throws HibernateException {
				SQLQuery query = session.createSQLQuery(sql);
				query.setString(0, userId);
				BigDecimal total = (BigDecimal)query.addScalar("amount", new org.hibernate.type.BigDecimalType()).uniqueResult();
				if(total ==null){
					return BigDecimal.ZERO;
				}
				return total;
			}
		});
		return list;
	}


/*	public List<Map<String, Object>> findPrizeList(String userId,
			String startDate, String endDate, int start,int pageSize) {
		String sql = "select * from money_deal t,( select  distinct  t.user_id,t.create_date  from money_deal t "+
					" where t.subject_type='1' "+
				//
					" order by t.create_date "+
					" limit "+start+","+pageSize+
					" ) b where t.user_id=b.user_id and t.create_date=b.create_date  ";
		if(userId != null && !"".equals(userId)){
			sql += " and t.user_id = '"+userId+"'";
		}
		//sql += " limit "+start+","+pageSize;
		List list = getSession().createSQLQuery(sql).list();
		String user_id=null;
		Map map=null;
		List<Map> result=null;
		int ind=0;
		for (int i = 0; i < list.size(); i++) {
			Object[] object = (Object[]) list.get(i);
			if(user_id==null){
				user_id=(String)object[1];
				map=new HashMap();
				ind=0;
			}
			if(!user_id.equals(object[1])){
				user_id=(String)object[1];
				map=new HashMap();
				ind=0;
			}else{
				ind++;
			}
			map=arrayToMap(map,object);
			if(ind==1)map.put("income1",object[3]);
			if(ind==2)map.put("income2",object[3]);
			if(ind==3)map.put("income3",object[3]);
			if(ind==4){
				map.put("income4",object[3]);
				map.put("create_date",object[4]);
			}
			result.add(map);
			
		return result;
	}
	
		
	public int cnt(){
		
	}
	
	private Map<String,Object> arrayToMap(Map<String,Object> map,Object[] array){
		if(map.size()>0){
			return map;
		}else{
			//....
		}
		return map;
	}*/
}
